﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace HCDV.HCSP
{
    public class ObjIUD
    {
        /// <summary>
        /// SQL注入处理--没处理
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public static string sqlfieldc(string i)
        {
            return i.Replace("'", "");
        }

        /// <summary>
        /// 按指定，导入信息
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="imptablename"></param>
        /// <param name="impspfv"></param>
        /// <param name="improwstart"></param>
        /// <param name="impcellstart"></param>
        /// <param name="impfield"></param>
        /// <param name="oinfo"></param>
        /// <returns></returns>
        public static bool importTable(DataTable dt, string imptablename, string impspfv, string improwstart, string impcellstart, string impfield, out string oinfo)
        {
            int count = 0;
            oinfo = "";
            bool re = false;
            string insertsql = "INSERT INTO {0}({1})  VALUES ({2})";
            //默认的字段处理
            string spfields = "";
            string spfieldvs = "";
            if (!string.IsNullOrEmpty(impspfv))
            {
                string[] fv = impspfv.Split(',');
                for (int i = 0; i < fv.Length; i++)
                {
                    spfields += fv[i].Split(':')[0] + ",";
                    spfieldvs += "'" + fv[i].Split(':')[1] + "',";
                }
            }
            //要取的列数
            int ccount = impfield.Split(',').Length;
            impfield = spfields + impfield;
            int rowstart = int.Parse(improwstart);
            int cellstart = int.Parse(impcellstart);
            StringBuilder fvinfo;
            using (dbsimple db = new dbsimple())
            {
                for (int ri = rowstart; ri < dt.Rows.Count; ri++)
                {
                    fvinfo = new StringBuilder(spfieldvs);
                    for (int ci = cellstart; ci < ccount; ci++)
                    {
                        fvinfo.Append("'").Append(dt.Rows[ri][ci].ToString()).Append("',");
                    }

                    db.SpExe(string.Format(insertsql, imptablename, impfield, fvinfo.Remove(fvinfo.Length - 1, 1).ToString()));
                    count++;
                }
                re = true;
            }
            oinfo = " 记录数：" + count;
            return re;
        }
        public static double insertInfoBackId(Hashtable allinfo, out string erinfo)
        {
            return (double)insertInfo(allinfo, true, out erinfo);
        }
        public static bool insertInfo(Hashtable allinfo, out string erinfo)
        {
            return (bool)insertInfo(allinfo, false, out erinfo);
        }
        /// <summary>
        /// 根据tablename  tablemidname id f+fields  动态插入数据
        /// </summary>
        /// <param name="allinfo"></param>
        /// <returns></returns>
        public static object insertInfo(Hashtable allinfo, bool backid, out string erinfo)
        {
            erinfo = "";
            string tablename = allinfo["tablename"].ToString();
            string tablemidname = "id";
            string id = "-1";

            if (allinfo.Contains("tablemidname")) tablemidname = allinfo["tablemidname"].ToString();
            if (allinfo.Contains("id")) id = allinfo["id"].ToString();

            string insertsql = "INSERT INTO {0}({1})  VALUES ({2})";
            StringBuilder fields = new StringBuilder("");
            StringBuilder values = new StringBuilder("");
            IDictionaryEnumerator en = allinfo.GetEnumerator();  //  遍历哈希表所有的键,读出相应的值
            bool isfirst = true;
            string fieldname = "";
            while (en.MoveNext())
            {
                fieldname = en.Key.ToString();

                if (fieldname.StartsWith("f"))//f insert+update
                {
                    fieldname = fieldname.Substring(1);
                }
                else if (fieldname.StartsWith("if"))//if insert
                {
                    fieldname = fieldname.Substring(2);
                }
                else continue;
                if (isfirst)
                {
                    fields.Append(fieldname);
                    values.Append((en.Value == null || en.Value.ToString() == "") ? "NULL" : "'" + en.Value.ToString() + "'");
                    isfirst = false;
                }
                else
                {
                    fields.Append("," + fieldname);
                    values.Append((en.Value == null || en.Value.ToString() == "") ? ",NULL" : ",'" + en.Value.ToString() + "'");

                }
            }
            //如果主键不是ID(即是，要使用非自增主键idmain)
            if (tablemidname == "idmain")
            {
                fields.Append("," + tablemidname);
                //fields += "," + tablemidname;
                values.Append(id == "" ? ",NULL" : ",'" + id + "'");
                //values += id == "" ? ",NULL" : ",'" + id + "'";
            }
            insertsql = string.Format(insertsql, tablename, fields.ToString(), values.ToString());
            using (dbsimple db = new dbsimple())
            {
                if (backid == false)
                {
                    return db.SpExe(insertsql);
                }
                else return db.SpExeBackid(insertsql);
            }
        }
        /// <summary>
        /// 根据tablename  tablemidname id f+fields  动态更新数据
        /// </summary>
        /// <param name="allinfo"></param>
        /// <param name="erinfo"></param>
        /// <returns></returns>
        public static bool updateInfo(Hashtable allinfo, out string erinfo)
        {
            erinfo = "";
            string tablename = allinfo["tablename"].ToString();
            string tablemidname = allinfo["tablemidname"].ToString();
            string id = allinfo["id"].ToString();
            string sql = "UPDATE {0} set {1} where " + tablemidname + "='" + id + "'";
            StringBuilder updateinfo = new StringBuilder("");
            string value = "";
            IDictionaryEnumerator en = allinfo.GetEnumerator();  //  遍历哈希表所有的键,读出相应的值
            bool isfirst = true;
            string fieldname = "";
            while (en.MoveNext())
            {
                fieldname = en.Key.ToString();
                if (fieldname.StartsWith("f"))//f insert+update
                {
                    fieldname = fieldname.Substring(1);
                }
                else if (fieldname.StartsWith("uf"))//uf update
                {
                    fieldname = fieldname.Substring(2);
                }
                else continue;

                //以 hcrainself 开头 代表（关联更新） 不要加引号
                //以 hcrainnum 开头 代表（数值） 不要加引号
                if (en.Value != null && (en.Value.ToString().IndexOf("hcrainself") >= 0 || en.Value.ToString().IndexOf("hcrainnum") >= 0))
                { value = en.Value.ToString().Replace("hcrainself", "").Replace("hcrainnum", ""); }
                else
                {
                    value = (en.Value == null || en.Value.ToString() == "") ? "NULL" : "'" + en.Value.ToString() + "'";
                }




                if (isfirst)
                {
                    updateinfo.Append(fieldname + "=" + value);


                    isfirst = false;
                }
                else
                {
                    updateinfo.Append("," + fieldname + "=" + value);
                }
            }
            sql = string.Format(sql, tablename, updateinfo.ToString());
            using (dbsimple db = new dbsimple())
            {
                return db.SpExe(sql);
            }
        }




        /// <summary>
        /// 根据tablename  tablemidname id  fields  动态插入数据(直接字段，数据库非ID字段定义时，以f开始)
        /// </summary>
        /// <param name="allinfo"></param>
        /// <returns></returns>
        public static bool insert(Hashtable allinfo, out string erinfo)
        {
            erinfo = "";
            string tablename = allinfo["tablename"].ToString();
            string tablemidname = "id";
            string id = "-1";
            try
            {
                tablemidname = allinfo["tablemidname"].ToString();
                id = allinfo["id"].ToString();
            }
            catch (Exception ee)
            { }
            string insertsql = "INSERT INTO {0}({1})  VALUES ({2})";
            StringBuilder fields = new StringBuilder("");
            StringBuilder values = new StringBuilder("");
            IDictionaryEnumerator en = allinfo.GetEnumerator();  //  遍历哈希表所有的键,读出相应的值
            bool isfirst = true;
            string fieldname = "";
            while (en.MoveNext())
            {
                fieldname = en.Key.ToString();

                if (fieldname.StartsWith("f"))//f insert+update
                {
                    //fieldname = fieldname.Substring(1);
                }
                else if (fieldname.StartsWith("if"))//if insert
                {
                    fieldname = fieldname.Substring(1);
                }
                else continue;
                if (isfirst)
                {
                    fields.Append(fieldname);
                    if (en.Value == null)
                        values.Append(",NULL");
                    else
                        values.Append(en.Value.ToString() == "" ? "NULL" : "'" + en.Value.ToString() + "'");
                    isfirst = false;
                }
                else
                {
                    fields.Append("," + fieldname);
                    if (en.Value == null)
                        values.Append(",NULL");
                    else
                        values.Append(en.Value.ToString() == "" ? ",NULL" : ",'" + en.Value.ToString() + "'");

                }
            }
            //如果主键不是ID(即是，要使用非自增主键idmain)
            if (tablemidname == "idmain")
            {
                fields.Append("," + tablemidname);
                //fields += "," + tablemidname;
                values.Append(id == "" ? ",NULL" : ",'" + id + "'");
                //values += id == "" ? ",NULL" : ",'" + id + "'";
            }
            insertsql = string.Format(insertsql, tablename, fields.ToString(), values.ToString());
            using (dbsimple db = new dbsimple())
            {
                return db.SpExe(insertsql);
            }
        }
        /// <summary>
        /// 根据tablename  tablemidname id fields  动态更新数据(直接字段，数据库非ID字段定义时，以f开始)
        /// </summary>
        /// <param name="allinfo"></param>
        /// <param name="erinfo"></param>
        /// <returns></returns>
        public static bool update(Hashtable allinfo, out string erinfo)
        {
            erinfo = "";
            string tablename = allinfo["tablename"].ToString();
            string tablemidname = allinfo["tablemidname"].ToString();
            string id = allinfo["id"].ToString();
            string sql = "UPDATE {0} set {1} where " + tablemidname + "='" + id + "'";
            StringBuilder updateinfo = new StringBuilder("");
            string value = "";
            IDictionaryEnumerator en = allinfo.GetEnumerator();  //  遍历哈希表所有的键,读出相应的值
            bool isfirst = true;
            string fieldname = "";
            while (en.MoveNext())
            {
                fieldname = en.Key.ToString();
                if (fieldname.StartsWith("f"))//f insert+update
                {
                    //fieldname = fieldname.Substring(1);
                }
                else if (fieldname.StartsWith("uf"))//uf update
                {
                    fieldname = fieldname.Substring(1);
                }
                else continue;
                value = en.Value.ToString() == "" ? "NULL" : "'" + en.Value.ToString() + "'";
                if (isfirst)
                {
                    updateinfo.Append(fieldname + "=" + value);


                    isfirst = false;
                }
                else
                {
                    updateinfo.Append("," + fieldname + "=" + value);
                }
            }
            sql = string.Format(sql, tablename, updateinfo.ToString());
            using (dbsimple db = new dbsimple())
            {
                return db.SpExe(sql);
            }
        }


        /// <summary>
        /// 根据tablename  tablemidname id f+fields  动态删除数据
        /// </summary>
        /// <param name="allinfo"></param>
        /// <param name="erinfo"></param>
        /// <returns></returns>
        public static bool delInfo(Hashtable allinfo, out string erinfo)
        {
            erinfo = "";
            string tablename = allinfo["tablename"].ToString();
            string tablemidname = allinfo["tablemidname"].ToString();
            string id = allinfo["id"].ToString();
            string[] IDList = id.Split(',');
            StringBuilder IDStr = new StringBuilder();
            for (int i = 0; i < IDList.Length; i++)
            {

                IDStr.Append(IDList[i]).Append(',');
            }
            if (IDStr.Length < 2) { erinfo = "没有主键信息" + IDStr; return false; }
            var ids = IDStr.Remove(IDStr.Length - 1, 1).ToString();
            string need_update_afterdel = "";
            #region yw_baseinfo 等基础信息删除处理
            if (tablename.ToLower() == "yw_baseinfo")//本项目特定处理
            {
                using (dbsimple db = new dbsimple())
                {
                    var sql2 = "";
                    var type = db.QueryFV("select type from yw_baseinfo where id in (" + ids + ") limit 0,1");
                    if (type == "5")//地块区域
                    {
                        sql2 = "select fv1 from yw_info where type =2021042601 and fv2 in (" + ids + ") limit 0,1 ";
                        if (!string.IsNullOrEmpty(db.QueryFV(sql2))) { erinfo = "区-数据正在使用中，无法删除"; return false; }
                    }
                    else if (type == "20210424")//栏圈
                    {
                        sql2 = "select fv1 from yw_info where type =2021042601 and fv9 in (" + ids + ") limit 0,1 ";
                        if (!string.IsNullOrEmpty(db.QueryFV(sql2))) { erinfo = "栏圈-数据正在使用中，无法删除"; return false; }
                    }
                    else if (type == "20210425")//屠宰组
                    {
                        sql2 = "select fv1 from yw_info where type in(2021042602,2021042603) and fv4 in (" + ids + ") limit 0,1 ";
                        if (!string.IsNullOrEmpty(db.QueryFV(sql2))) { erinfo = "屠宰组-数据正在使用中，无法删除"; return false; }
                    }
                    else if (type == "12")//商超管理
                    {
                        sql2 = "select fv1 from yw_info where type in(2021042605) and fv10 in (" + ids + ") limit 0,1 ";
                        if (!string.IsNullOrEmpty(db.QueryFV(sql2))) { erinfo = "客户-数据正在使用中，无法删除"; return false; }
                    }
                }
            }
            else if (tablename.ToLower() == "herp_customer")//本项目特定处理
            {
                using (dbsimple db = new dbsimple())
                {
                    var sql2 = "";
                    var type = db.QueryFV("select type from herp_customer where id in (" + ids + ") limit 0,1");
                    if (type == "客户")//
                    {
                        sql2 = "select fv1 from yw_info where type =2021042601 and fv4 in (" + ids + ") limit 0,1 ";
                        if (!string.IsNullOrEmpty(db.QueryFV(sql2))) { erinfo = "数据正在使用中，无法删除"; return false; }
                    }
                    else if (type == "供应商")//供应商
                    {
                        sql2 = "select fv1 from yw_info where type =2021042601 and fv6 in (" + ids + ") limit 0,1 ";
                        if (!string.IsNullOrEmpty(db.QueryFV(sql2))) { erinfo = "数据正在使用中，无法删除"; return false; }
                    }

                }
            }
            else if (tablename.ToLower() == "herp_storehouse")//本项目特定处理
            {
                using (dbsimple db = new dbsimple())
                {
                    var sql2 = "";
                    sql2 = "select fv1 from yw_info where type =2021042604 and fv6 in (" + ids + ") limit 0,1 ";
                    if (!string.IsNullOrEmpty(db.QueryFV(sql2))) { erinfo = "库-数据正在使用中，无法删除"; return false; }
                }
            }
            #endregion
            #region 业务流程信息删除检查
            else if (tablename.ToLower() == "yw_info")//本项目特定处理
            {
                using (dbsimple db = new dbsimple())
                {
                    var type = db.QueryFV("select type from yw_info where id in (" + ids + ") limit 0,1");
                    if (type == "2021042601")//生猪收购
                    {
                        var hasids = db.QueryFV("select id from yw_info where type =2021042602 and fv1 in (SELECT fv1 from yw_info where id in (" + ids + ") ) limit 0,1");
                        if (!string.IsNullOrEmpty(hasids)) { erinfo = "存在出栏记录，无法删除"; return false; }
                    }
                    else if (type == "2021042602")//出栏记录
                    {

                        var hasids = db.QueryFV("select id from yw_info where type =2021042603 and fv2 in  (" + ids + ")  limit 0,1");
                        if (!string.IsNullOrEmpty(hasids)) { erinfo = "存在屠宰记录，无法删除"; return false; }
                        var fv1values = "";
                        var dtfv1 = db.Query("select fv1 from yw_info where id in   (" + ids + ") ");
                  
                        for (int ti = 0; ti < dtfv1.Rows.Count; ti++)
                        {
                            fv1values += $"'{dtfv1.Rows[ti][0]}',";
                        }
                        fv1values = fv1values.Remove(fv1values.Length - 1, 1).ToString();
                        // (SELECT IFNULL(sum(b.nv1), 0.0) from yw_info b where b.fv1 = a.fv1 and b.type = 2021042602) as hasout
                        need_update_afterdel = $@"update yw_info a
 set  a.nv2= (SELECT IFNULL(sum(sumnv1), 0.0) from (
SELECT IFNULL(sum(b.nv1), 0.0) sumnv1,b.fv1  from yw_info b where  b.type = 2021042602 and b.fv1 in  ({fv1values}) GROUP BY b.fv1
) bb where  a.fv1=bb.fv1  )
     where a.fv1 in ({fv1values})";
                    }
                    else if (type == "2021042603")//屠宰记录
                    {
                        var hasids = db.QueryFV("select id from yw_info where type in(2021042604,2021042613,2021042605) and fv3 in (SELECT fv3 from yw_info where id in (" + ids + ") ) limit 0,1");
                        if (!string.IsNullOrEmpty(hasids)) { erinfo = "存在销售/入库记录，无法删除"; return false; }
                    }
                    else if (type == "2021042604" || type == "2021042613")//库记录
                    {
                        var hasids = db.QueryFV("select id from yw_info where type in(2021042605) and fv3 in (SELECT fv3 from yw_info where id in (" + ids + ") ) limit 0,1");
                        if (!string.IsNullOrEmpty(hasids)) { erinfo = "存在销售记录，无法删除"; return false; }
                    }
                }
            }

            #endregion
            string sql = "delete from  {0}  where " + tablemidname + " in (" + ids + ")";

            sql = string.Format(sql, tablename);
            using (dbsimple db = new dbsimple())
            {
                var re = db.SpExe(sql);
                if (re && !string.IsNullOrEmpty(need_update_afterdel))
                    db.SpExe(need_update_afterdel);
                return re;


            }
        }
    }
}
